﻿using AlibabaCloud.SDK.Dingtalkservice_group_1_0.Models;
using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.IT
{
    public partial class IT_LocalMaterialBusiness : BaseBusiness<IT_LocalMaterial>, IIT_LocalMaterialBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        readonly IOperatorDept _opdept;
        public IT_LocalMaterialBusiness(IDbAccessor db, IOperator @operator, IOperatorDept @opdept)
            : base(db)
        {
            _operator = @operator;
            _opdept = @opdept;
        }

        #region 外部接口

        public async Task<PageResult<IT_LocalMaterial>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<IT_LocalMaterial>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<IT_LocalMaterial, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PageResult<IT_LocalMaterial>> GetDataListByMaterialId(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var search = input.Search;
            q = q.Include(i => i.Location).Include(i => i.Material);

            //筛选
            if (!search.Keyword.IsNullOrEmpty())
            {
                q = q.Where(w => w.MaterialId == search.Keyword);
            }
            if (!search.Condition.IsNullOrEmpty())
            {
                q = q.Where(w => w.StorId == search.Condition);
            }

            return await q.GetPageResultAsync(input);
        }

        public async Task<IT_LocalMaterial> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(IT_LocalMaterial data)
        {
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(IT_LocalMaterial data)
        {
            await UpdateAsync(data);
        }

        public async Task DeleteDataAsync(List<string> ids)
        {
            await DeleteAsync(ids);
        }

        public async Task<IT_LocalMaterial> GetByLocalMaterial(string local)
        {
            //return await GetIQueryable.where(w=>local.Contains(w.Id));
            return await this.GetIQueryable().SingleOrDefaultAsync(w => w.LocalId == local);
        }

      
        public  async Task<PageResult<IT_LocalMaterialDTO>> GetDataListAsyncDtoList(PageInput<IT_LocalMaterialConditionDTO> input)
        {
            Expression<Func<IT_LocalMaterial, PB_Material, Base_Department, PB_Location, Base_UnitItems, PB_Rack, PB_StorArea, IT_LocalMaterialDTO>> select = (a, b, c, d, e, f, g) => new IT_LocalMaterialDTO
            {
                Code = b.Code,//物料编号
                Name = b.Name,///物料名称
                Spce = b.Spec,///物料规格
                StorageName = c.Name,///物料名称
                LocationName = d.Name,///货位
                Inventoryquantity = a.Nums_N + a.Nums_O,///库存数量
                Availablequantity = (a.Nums_N + a.Nums_O) == 0 ? 0 : (a.Nums_N + a.Nums_O - a.Nums_R) < 0 ? 0 : (a.Nums_N + a.Nums_O - a.Nums_R),//可用数量
                storageIdName = e.Name,//仓储单位
                Max = b.Max,//物料上限数量
                Min = b.Min,//物料下限数量
                //Nums_R = (a.Nums_N + a.Nums_O)-(a.Nums_N + a.Nums_O - a.Nums_R) <0?0:a.Nums_R,
                Inventory_Status = (a.Nums_N + a.Nums_O) >= b.Max ? "exceed_max" : (a.Nums_N + a.Nums_O) == 0 || (a.Nums_N + a.Nums_O) - a.Nums_R == 0 ? "empty" : (a.Nums_N + a.Nums_O) <= b.Min ? "below_min" : b.Min == null ? "下限为null法判断" : "normalcy",
                MaterialImg = b.MaterialImg,//物料img
                RackIdName = f.Name,
                AreaName = g.Name,
                IsSingle = b.IsSingle,
                RackId = f.Id,
                Weight = b.Weight.IsNullOrEmpty()?0: b.Weight,
                TotalWeight = a.ScanNum * (b.Weight.IsNullOrEmpty() ? 0 : b.Weight)
            };
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<IT_LocalMaterial>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on a.StorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Location>() on a.LocalId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_UnitItems>() on b.storageId equals e.Id into be
                    from e in be.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Rack>() on d.RackId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    join g in Db.GetIQueryable<PB_StorArea>() on d.AreaId equals g.Id into dg
                    from g in dg.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d,e,f,g);

           // 条件筛选
         
            if (!search.LocationName.IsNullOrEmpty())
            {
                q = q.Where(x => x.LocationName==search.LocationName);
            }
            if (!search.StorageName.IsNullOrEmpty()) {
                q = q.Where(m=>m.StorId .Contains( search.StorageName));
            }
            if (!search.Keyword.IsNullOrEmpty())
            {
                q = q.Where(m => m.Code.Contains(search.Keyword)||m.Name.Contains(search.Keyword) );
            }
            if (!search.AreaName.IsNullOrEmpty()) {
                q = q.Where(m => m.AreaId.Contains(search.AreaName));
            }
            if (!search.RackName.IsNullOrEmpty()) {
                q = q.Where(m=>m.RackId==search.RackName);
            }
            q = q.Where(m=>m.Inventoryquantity>0);
            if (!search.IsSingle.IsNullOrEmpty())
            {
                q = q.Where(m => m.IsSingle==true);
            }
            return await q.GetPageResultAsync(input);
        }

        /// <summary>
        /// 查看货位占用量信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<AmountOccupied>> GetAmountOccupiedListAsync(PageInput<AmountOccupiedConditionDTO> input)
        {
            Expression<Func<PB_PickUpList, PB_PickUpMain, PB_Material, AmountOccupied>> select = (a, b, c) => new AmountOccupied
            {
                Id = a.Id,
                PickMainId = a.ParentId,
                BuildingId = b.BuildingId,
                DeliveryTime = b.DeliveryTime,
                Nums_R = a.StockVolume - (a.ReadyNums == null ? 0 : a.ReadyNums),
                LocationId = a.LocationId,
                MaterialId = a.MaterialId,
                MaterialCode = c.Code,
                MaterialName = c.Name,
                MaterialSpec = c.Spec,
                ProductCode = c.ProductCode,
                Status = b.Status
            };
            var search = input.Search;
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_PickUpList>().AsExpandable()
                    join b in Db.GetIQueryable<PB_PickUpMain>() on a.ParentId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_Material>() on a.MaterialId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @select.Invoke(a, b, c);
            q = q.Where(x => x.MaterialId == search.MaterialId && x.LocationId == search.LocalId && x.Status < 3 && x.Nums_R > 0);
            return await q.GetPageResultAsync(input);
        }


        /// <summary>
        /// 大屏显示出入库监控
        /// </summary>
        /// <returns>出入库监控</returns>
        public async Task<List<IT_LocalMaterialOutInStatusDTO>> GetOutInStatus(int Days)
        {
            string strSQL = @"SELECT * 
FROM
((SELECT 
en.`Name` AS Type,os.OutTime AS CreateTime,m.`Name` AS Material,m.Spec AS Spec,od.OutNum AS Count,pl.ReadyNums AS ScanVal,u.RealName AS Creator,
CONCAT('[ ',IFNULL(sa.`Name`,''),'/',IFNULL(r.`Name`,''),'/',IFNULL(loc.`Name`,''),' ]') AS Location,pm.ToReviewStatus AS IsError
FROM TD_OutStorDetail od
JOIN TD_OutStorage os ON od.OutStorId=os.Id AND TO_DAYS(CURDATE())-TO_DAYS(DATE(os.OutTime))=" + Days.ToString()+ @"
JOIN PB_Material m ON od.MaterialId=m.Id
LEFT JOIN PB_PickUpMain pm ON os.RefCode=pm.Id
LEFT JOIN PB_PickUpList pl ON pl.ParentId=pm.Id
LEFT JOIN Base_User u ON pl.CreatorId=u.Id
LEFT JOIN PB_Storage st ON od.StorId=st.Id
LEFT JOIN PB_StorArea sa ON od.AreaId=sa.Id
LEFT JOIN PB_Rack r ON od.RackId=r.Id
LEFT JOIN PB_Location loc ON od.LocalId=loc.Id
LEFT JOIN Base_EnumItem en ON en.`Code`=os.OutType AND en.EnumCode='OutStorageType'
ORDER BY os.OutTime DESC,os.OutType)
UNION ALL
(SELECT
en.`Name` AS Type,it.InStorTime AS CreateTime,m.`Name` AS Material,m.Spec AS Spec,id.Num AS Count,rd.InNum AS ScanVal,u.RealName AS Creator,
CONCAT('[ ',IFNULL(sa.`Name`,''),'/',IFNULL(r.`Name`,''),'/',IFNULL(loc.`Name`,''),' ]') AS Location,0 AS IsError
FROM TD_InStorDetail id
JOIN TD_InStorage it ON id.InStorId=it.Id AND TO_DAYS(CURDATE())-TO_DAYS(DATE(it.InStorTime))=" + Days.ToString() + @"
JOIN PB_Material m ON id.MaterialId=m.Id
LEFT JOIN TD_Receiving rv ON it.RecId=rv.Id
LEFT JOIN TD_RecDetail rd ON rd.RecId=rv.Id
LEFT JOIN Base_User u ON rd.CreatorId=u.Id
LEFT JOIN PB_Location loc ON id.LocalId=loc.Id
LEFT JOIN PB_Storage st ON loc.StorId=st.Id
LEFT JOIN PB_StorArea sa ON loc.AreaId=sa.Id
LEFT JOIN PB_Rack r ON loc.RackId=r.Id
LEFT JOIN Base_EnumItem en ON en.`Code`=it.InType AND en.EnumCode='InStorageType' 
ORDER BY it.InStorTime DESC,it.InType)) t
ORDER BY t.CreateTime DESC,t.Type
LIMIT 20";
            var res = await Db.GetListBySqlAsync<IT_LocalMaterialOutInStatusDTO>(strSQL);
            if (res.Count > 0)
            {
                res.ForEach(aData =>
                {
                    aData.Count = Convert.ToDouble(aData.Count.ToString("#0.##"));
                    aData.ScanVal = Convert.ToDouble(aData.ScanVal.ToString("#0.##"));
                });

            }
            return res;
        }

        /// <summary>
        /// 明细入总表
        /// </summary>
        /// <returns>总表ID</returns>
        public List<string> Add()
        {
            string sql = @"SELECT
m.`Name` AS Material,m.Spec AS Spec,id.Num AS Count,rd.InNum AS ScanVal
FROM IT_LocalDetail id
JOIN TD_InStorage it ON id.InStorId=it.Id
LEFT JOIN PB_Material m ON id.MaterialId=m.Id
LEFT JOIN TD_Receiving rv ON it.RecId=rv.Id
LEFT JOIN TD_RecDetail rd ON rd.RecId=rv.Id";

            List<string> ids = new List<string>();
            string strSQL = @"SELECT b.*
FROM 
IT_LocalMaterial a
right JOIN IT_LocalDetail b ON a.StorId=b.StorId and a.LocalId=b.LocalId and a.MaterialId=b.MaterialId and a.StorId is null";
            List<IT_LocalDetail> list = Db.GetListBySql<IT_LocalDetail>(strSQL);
            List<IT_LocalMaterial> res = list.GroupBy(m => new { m.StorId, m.LocalId, m.MaterialId,m.OldOrNew }).Select(g => new IT_LocalMaterial
            {
                //ProjectId = g.Key.ProjectId,
                //ProjectName = g.FirstOrDefault().ProjectName,
                //ReturnRate = g.Sum(r => r.ReturnRate) / g.Count(),
                //DestoriedRate = g.Sum(r => r.DestoriedRate) / g.Count()
                Id = IdHelper.GetId(),
                MaterialId = g.Key.MaterialId,
                StorId = g.Key.StorId,
                LocalId = g.Key.LocalId,
                Batch = g.FirstOrDefault().BatchNo,
                CreateTime = g.FirstOrDefault().CreateTime,
                CreatorId = g.FirstOrDefault().CreatorId,
                Deleted = g.FirstOrDefault().Deleted,

            }).ToList();
            
            return ids;
        }

        /// <summary>
        /// PDA扫码获取货区货架货位
        /// </summary>
        /// <param name="locationQrCode">货位二维码</param>
        /// <param name="materialId">物料ID</param>
        /// <returns></returns>
        public async Task<List<IT_LocalMaterialDTO>> GetPDADataListAsyncDtoList(string locationQrCode,string materialId)
        {
            if (locationQrCode.Split(',')[0].ToString()=="06")
            {
                Expression<Func<IT_LocalMaterial, PB_Material, Base_Department, PB_Location, Base_UnitItems, PB_Rack, PB_StorArea, IT_LocalMaterialDTO>> select = (a, b, c, d, e, f, g) => new IT_LocalMaterialDTO
                {
                    Code = b.Code,//物料编号
                    Name = b.Name,//物料名称
                    Spce = b.Spec,//物料规格
                    StorageName = c.Name,//物料名称
                    MaterialQrcode = b.QrCode, //物料二维码
                    LocationQrCode = d.QrCode, //货位二维码
                    LocationName = d.Name,//货位
                    //Inventoryquantity = a.Nums_N + a.Nums_O,//库存数量
                    //Availablequantity = (a.Nums_N + a.Nums_O) == 0 ? 0 : (a.Nums_N + a.Nums_O - a.Nums_R) < 0 ? 0 : (a.Nums_N + a.Nums_O - a.Nums_R),//可用数量
                    //storageIdName = e.Name,//仓储单位
                    //Max = b.Max,//物料上限数量
                    //Min = b.Min,//物料下限数量
                    Nums_R = (a.Nums_N + a.Nums_O) - (a.Nums_N + a.Nums_O - a.Nums_R) < 0 ? 0 : a.Nums_R,
                    Inventory_Status = (a.Nums_N + a.Nums_O) >= b.Max ? "exceed_max" : (a.Nums_N + a.Nums_O) == 0 || (a.Nums_N + a.Nums_O) - a.Nums_R == 0 ? "empty" : (a.Nums_N + a.Nums_O) <= b.Min ? "below_min" : b.Min == null ? "下限为null法判断" : "normalcy",
                    //MaterialImg = b.MaterialImg,//物料img
                    RackIdName = f.Name,
                    AreaName = g.Name,
                    TargetName = g.Name + "/" + f.Name + "/" + d.Name

                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<IT_LocalMaterial>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        join c in Db.GetIQueryable<Base_Department>() on a.StorId equals c.Id into ac
                        from c in ac.DefaultIfEmpty()
                        join d in Db.GetIQueryable<PB_Location>() on a.LocalId equals d.Id into ad
                        from d in ad.DefaultIfEmpty()
                        join e in Db.GetIQueryable<Base_UnitItems>() on b.storageId equals e.Id into be
                        from e in be.DefaultIfEmpty()
                        join f in Db.GetIQueryable<PB_Rack>() on d.RackId equals f.Id into af
                        from f in af.DefaultIfEmpty()
                        join g in Db.GetIQueryable<PB_StorArea>() on d.AreaId equals g.Id into dg
                        from g in dg.DefaultIfEmpty()
                        select @select.Invoke(a, b, c, d, e, f, g);
                var list =  await q.Where(x => x.MaterialId.Equals(materialId) && x.LocationQrCode.Equals(locationQrCode)).ToListAsync();

                if (list.Count() >= 1)
                {
                    return list;
                }
                else
                {
                    throw new BusException("该货位没有您想要的物料信息,请重新扫描");
                }
                
            }
            else
            {
                throw new BusException("请扫描或输入正确的货位二维码信息");
            }
            

        }

        public async Task<List<IT_LocalMaterialDTO>> GetDataListAsynclist()
        {
            Expression<Func<IT_LocalMaterial, PB_Material, Base_Department, PB_Location, Base_UnitItems, PB_Rack, PB_StorArea, IT_LocalMaterialDTO>> select = (a, b, c, d, e, f, g) => new IT_LocalMaterialDTO
            {
                Code = b.Code,//物料编号
                Name = b.Name,///物料名称
                Spce = b.Spec,///物料规格
                StorageName = c.Name,///物料名称
                LocationName = d.Name,///货位
                Inventoryquantity = a.Nums_N + a.Nums_O,///库存数量
                Availablequantity = (a.Nums_N + a.Nums_O) == 0 ? 0 : (a.Nums_N + a.Nums_O - a.Nums_R) < 0 ? 0 : (a.Nums_N + a.Nums_O - a.Nums_R),//可用数量
                storageIdName = e.Name,//仓储单位
                Max = b.Max,//物料上限数量
                Min = b.Min,//物料下限数量
                Nums_R = (a.Nums_N + a.Nums_O) - (a.Nums_N + a.Nums_O - a.Nums_R) < 0 ? 0 : a.Nums_R,
                Inventory_Status = (a.Nums_N + a.Nums_O) >= b.Max ? "exceed_max" : (a.Nums_N + a.Nums_O) == 0 || (a.Nums_N + a.Nums_O) - a.Nums_R == 0 ? "empty" : (a.Nums_N + a.Nums_O) <= b.Min ? "below_min" : b.Min == null ? "下限为null法判断" : "normalcy",
                MaterialImg = b.MaterialImg,//物料img
                RackIdName = f.Name,
                AreaName = g.Name,
                Weight = b.Weight.IsNullOrEmpty() ? 0 : b.Weight,
                TotalWeight = a.ScanNum * (b.Weight.IsNullOrEmpty() ? 0 : b.Weight)
            };
            
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<IT_LocalMaterial>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_Department>() on a.StorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Location>() on a.LocalId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<Base_UnitItems>() on b.storageId equals e.Id into be
                    from e in be.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Rack>() on d.RackId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    join g in Db.GetIQueryable<PB_StorArea>() on d.AreaId equals g.Id into dg
                    from g in dg.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e, f, g);

            var res = q.ToListAsync();
            return await res;
        }

        #endregion

        #region   库存结存

        /// <summary>
        /// 期初汇算结存数据
        /// </summary>
        /// <returns></returns>
        [Transactional]
        public async Task GetStartInventoryBalanceAsync()
        {
            //清除结存表数据
            string sqlMain = @"delete from IT_MaterialBalance";
            await ExecuteSqlAsync(sqlMain);

            //获取当前月第一天
            DateTime dt = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date;
            string staryear = dt.Year.ToString();
            string starmonth = dt.Month.ToString();

            //获取登录人所属工厂下的所有仓库
            List<string> StorIds = await _opdept.GetStorString();

            //获取第一个入库单的时间
            TD_InStorage instordata = await Db.GetIQueryable<TD_InStorage>().Where(x => StorIds.Contains(x.StorId)).OrderBy(x => x.InStorTime).FirstOrDefaultAsync();
            //获取期初日期
            DateTime instortime = Convert.ToDateTime(instordata.InStorTime.ToString("yyyy-MM-dd"));
            string endyear = instortime.Year.ToString();
            string endymonth = instortime.Month.ToString();

            //计算月数差及每次结存的时间开始日期和结束日期
            int month = (Convert.ToInt32(staryear) - Convert.ToInt32(endyear)) * 12 + (Convert.ToInt32(starmonth) - Convert.ToInt32(endymonth));

            //第一个月的月初
            DateTime onedt = Convert.ToDateTime(endyear + "-" + endymonth + "-" + "01");

            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            List<InStorDTO> Inlist = new List<InStorDTO>();

            List<OutStorDTO> Outlist = new List<OutStorDTO>();

            List<IT_MaterialBalance> MaterialBalancelist = new List<IT_MaterialBalance>();

            //循环计算
            for (int i = 0; i < month; i++)
            {
                DateTime sitemdt = onedt.AddMonths(i);
                DateTime eitemdt = sitemdt.AddMonths(1).AddSeconds(-1);
                List<StorMaterialDTO> listmaterial = new List<StorMaterialDTO>();
                //入库情况
                Expression<Func<TD_InStorDetail, TD_InStorage, InStorDTO>> select = (a, b) => new InStorDTO
                {
                    Year = b.InStorTime.Year,
                    Month = b.InStorTime.Month,
                    InStorTime = b.InStorTime,
                    StorId = a.StorId,
                    MaterialId = a.MaterialId,
                    Num = a.Num
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<TD_InStorDetail>().AsExpandable()
                        join b in Db.GetIQueryable<TD_InStorage>() on a.InStorId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);
                Inlist = await q.Where(x => StorIds.Contains(x.StorId) && x.InStorTime >= sitemdt && x.InStorTime <= eitemdt).ToListAsync();
                var instor = Inlist.GroupBy(x => new { x.Year, x.Month, x.StorId, x.MaterialId }).Select(x => new { Year = x.Key.Year, Month = x.Key.Month, StorId = x.Key.StorId, MaterialId = x.Key.MaterialId, Nums = x.Sum(j => j.Num) }).ToList();
                foreach (var item in instor)
                {
                    StorMaterialDTO smdata = new StorMaterialDTO();
                    smdata.StorId = item.StorId;
                    smdata.MaterialId = item.MaterialId;
                    listmaterial.Add(smdata);
                }

                //出库情况
                Expression<Func<TD_OutStorDetail, TD_OutStorage, OutStorDTO>> selectout = (a, b) => new OutStorDTO
                {
                    Year = b.OutTime.Year,
                    Month = b.OutTime.Month,
                    OutTime = b.OutTime,
                    StorId = a.StorId,
                    MaterialId = a.MaterialId,
                    OutNum = a.OutNum
                };
                selectout = selectout.BuildExtendSelectExpre();
                var qout = from a in Db.GetIQueryable<TD_OutStorDetail>().AsExpandable()
                           join b in Db.GetIQueryable<TD_OutStorage>() on a.OutStorId equals b.Id into ab
                           from b in ab.DefaultIfEmpty()
                           select @selectout.Invoke(a, b);
                Outlist = await qout.Where(x => StorIds.Contains(x.StorId) && x.OutTime >= sitemdt && x.OutTime <= eitemdt).ToListAsync();
                var outstor = Outlist.GroupBy(x => new { x.Year, x.Month, x.StorId, x.MaterialId }).Select(x => new { Year = x.Key.Year, Month = x.Key.Month, StorId = x.Key.StorId, MaterialId = x.Key.MaterialId, Nums = x.Sum(j => j.OutNum) }).ToList();
                foreach (var item in outstor)
                {
                    StorMaterialDTO smdata = new StorMaterialDTO();
                    smdata.StorId = item.StorId;
                    smdata.MaterialId = item.MaterialId;
                    listmaterial.Add(smdata);
                }
                //当前月的出入库物料
                var StorMdata = listmaterial.GroupBy(x => new { x.StorId, x.MaterialId }).Select(x => new { StorId = x.Key.StorId, MaterialId = x.Key.MaterialId }).ToList();
                string itemyear = eitemdt.Year.ToString();
                string itemMonth = (eitemdt.Month - 1) < 10 ? ("0" + (sitemdt.Month - 1)) : (sitemdt.Month - 1).ToString();

                foreach (var item in StorMdata)
                {
                    IT_MaterialBalance mbdata = new IT_MaterialBalance();
                    mbdata.Id = IdHelper.GetId();
                    mbdata.Year = sitemdt.Year.ToString();
                    mbdata.Month = sitemdt.Month < 10 ? ("0" + sitemdt.Month) : sitemdt.Month.ToString();
                    mbdata.YearMonth = sitemdt.ToString("yyyy-MM");
                    mbdata.StorId = item.StorId;
                    mbdata.MaterialId = item.MaterialId;
                    var lastmonthdata = MaterialBalancelist.Where(x => x.Year == itemyear && x.Month == itemMonth && x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                    if (lastmonthdata != null)
                    {
                        mbdata.LastMonth = lastmonthdata.ThisNums;
                    }
                    else
                    {
                        mbdata.LastMonth = 0;
                    }
                    var initem = instor.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                    if (initem != null)
                    {
                        mbdata.InNums = initem.Nums;
                    }
                    else
                    {
                        mbdata.InNums = 0;
                    }
                    var outitem = outstor.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                    if (outitem != null)
                    {
                        mbdata.OutNums = outitem.Nums;
                    }
                    else
                    {
                        mbdata.OutNums = 0;
                    }
                    mbdata.ThisNums = (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums) < 0 ? 0 : (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums);
                    mbdata.CreatorId = _operator.UserId;
                    mbdata.CreatorRealName = userdata.RealName;
                    mbdata.CreateTime = DateTime.Now;
                    mbdata.Deleted = false;
                    MaterialBalancelist.Add(mbdata);
                }

                List<IT_MaterialBalance> MBlist = new List<IT_MaterialBalance>();
                List<IT_MaterialBalance> listMaterialBalance = MaterialBalancelist.Where(x => x.Year == itemyear && x.Month == itemMonth).ToList();
                foreach (var item in listMaterialBalance)
                {
                    var itemdata = StorMdata.Where(x => x.MaterialId == item.MaterialId && x.StorId == item.StorId).FirstOrDefault();
                    if (itemdata == null)
                    {
                        MBlist.Add(item);
                    }
                }

                foreach (var item in MBlist)
                {
                    IT_MaterialBalance mbdata = new IT_MaterialBalance();
                    mbdata.Id = IdHelper.GetId();
                    mbdata.Year = sitemdt.Year.ToString();
                    mbdata.Month = sitemdt.Month < 10 ? ("0" + sitemdt.Month) : sitemdt.Month.ToString();
                    mbdata.YearMonth = sitemdt.ToString("yyyy-MM");
                    mbdata.StorId = item.StorId;
                    mbdata.MaterialId = item.MaterialId;
                    mbdata.LastMonth = item.ThisNums;
                    mbdata.InNums = 0;
                    mbdata.OutNums = 0;
                    mbdata.ThisNums = (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums) < 0 ? 0 : (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums);
                    mbdata.CreatorId = _operator.UserId;
                    mbdata.CreatorRealName = userdata.RealName;
                    mbdata.CreateTime = DateTime.Now;
                    mbdata.Deleted = false;
                    MaterialBalancelist.Add(mbdata);
                }
            }

            await Db.InsertAsync<IT_MaterialBalance>(MaterialBalancelist);

        }

        /// <summary>
        /// 上月结存
        /// </summary>
        /// <returns></returns>
        [Transactional]
        public async Task GetLastMonthBalanceAsync()
        {
            //获取当前月第一天
            DateTime dt = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date;
            string staryear = dt.Year.ToString();
            string starmonth = dt.Month.ToString();

            string YearMonth = staryear + "-" + (dt.AddMonths(-1).Month < 10 ? ("0" + dt.AddMonths(-1).Month) : dt.AddMonths(-1).Month.ToString());

            List<IT_MaterialBalance> listdata = await Db.GetIQueryable<IT_MaterialBalance>().Where(x => x.YearMonth == YearMonth).ToListAsync();
            if (listdata.Count > 0)
            {
                throw new BusException("上月结存已存在");
            }

            //上月月初
            DateTime startime = dt.AddMonths(-1);
            //上月月末
            DateTime endtime = dt.AddSeconds(-1);

            //获取登录人所属工厂下的所有仓库
            List<string> StorIds = await _opdept.GetStorString();

            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            List<StorMaterialDTO> listmaterial = new List<StorMaterialDTO>();
            //入库情况
            Expression<Func<TD_InStorDetail, TD_InStorage, InStorDTO>> select = (a, b) => new InStorDTO
            {
                Year = b.InStorTime.Year,
                Month = b.InStorTime.Month,
                InStorTime = b.InStorTime,
                StorId = a.StorId,
                MaterialId = a.MaterialId,
                Num = a.Num
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<TD_InStorDetail>().AsExpandable()
                    join b in Db.GetIQueryable<TD_InStorage>() on a.InStorId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var Inlist = await q.Where(x => StorIds.Contains(x.StorId) && x.InStorTime >= startime && x.InStorTime <= endtime).ToListAsync();
            var instor = Inlist.GroupBy(x => new { x.Year, x.Month, x.StorId, x.MaterialId }).Select(x => new { Year = x.Key.Year, Month = x.Key.Month, StorId = x.Key.StorId, MaterialId = x.Key.MaterialId, Nums = x.Sum(j => j.Num) }).ToList();
            foreach (var item in instor)
            {
                StorMaterialDTO smdata = new StorMaterialDTO();
                smdata.StorId = item.StorId;
                smdata.MaterialId = item.MaterialId;
                listmaterial.Add(smdata);
            }

            //出库情况
            Expression<Func<TD_OutStorDetail, TD_OutStorage, OutStorDTO>> selectout = (a, b) => new OutStorDTO
            {
                Year = b.OutTime.Year,
                Month = b.OutTime.Month,
                OutTime = b.OutTime,
                StorId = a.StorId,
                MaterialId = a.MaterialId,
                OutNum = a.OutNum
            };
            selectout = selectout.BuildExtendSelectExpre();
            var qout = from a in Db.GetIQueryable<TD_OutStorDetail>().AsExpandable()
                       join b in Db.GetIQueryable<TD_OutStorage>() on a.OutStorId equals b.Id into ab
                       from b in ab.DefaultIfEmpty()
                       select @selectout.Invoke(a, b);
            var Outlist = await qout.Where(x => StorIds.Contains(x.StorId) && x.OutTime >= startime && x.OutTime <= endtime).ToListAsync();
            var outstor = Outlist.GroupBy(x => new { x.Year, x.Month, x.StorId, x.MaterialId }).Select(x => new { Year = x.Key.Year, Month = x.Key.Month, StorId = x.Key.StorId, MaterialId = x.Key.MaterialId, Nums = x.Sum(j => j.OutNum) }).ToList();
            foreach (var item in outstor)
            {
                StorMaterialDTO smdata = new StorMaterialDTO();
                smdata.StorId = item.StorId;
                smdata.MaterialId = item.MaterialId;
                listmaterial.Add(smdata);
            }

            //查询当前月的上月结存信息
            listdata = await Db.GetIQueryable<IT_MaterialBalance>().Where(x => x.YearMonth == startime.AddMonths(-1).ToString("yyyy-MM")).ToListAsync();

            List<IT_MaterialBalance> MaterialBalancelist = new List<IT_MaterialBalance>();
            //当前月的出入库物料
            var StorMdata = listmaterial.GroupBy(x => new { x.StorId, x.MaterialId }).Select(x => new { StorId = x.Key.StorId, MaterialId = x.Key.MaterialId }).ToList();

            foreach (var item in StorMdata)
            {
                IT_MaterialBalance mbdata = new IT_MaterialBalance();
                mbdata.Id = IdHelper.GetId();
                mbdata.Year = startime.Year.ToString();
                mbdata.Month = startime.Month < 10 ? ("0" + startime.Month) : startime.Month.ToString();
                mbdata.YearMonth = startime.ToString("yyyy-MM");
                mbdata.StorId = item.StorId;
                mbdata.MaterialId = item.MaterialId;
                var lastmonthdata = MaterialBalancelist.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                if (lastmonthdata != null)
                {
                    mbdata.LastMonth = lastmonthdata.ThisNums;
                }
                else
                {
                    mbdata.LastMonth = 0;
                }
                var initem = instor.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                if (initem != null)
                {
                    mbdata.InNums = initem.Nums;
                }
                else
                {
                    mbdata.InNums = 0;
                }
                var outitem = outstor.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                if (outitem != null)
                {
                    mbdata.OutNums = outitem.Nums;
                }
                else
                {
                    mbdata.OutNums = 0;
                }
                mbdata.ThisNums = (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums) < 0 ? 0 : (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums);
                mbdata.CreatorId = _operator.UserId;
                mbdata.CreatorRealName = userdata.RealName;
                mbdata.CreateTime = DateTime.Now;
                mbdata.Deleted = false;
                MaterialBalancelist.Add(mbdata);
            }

            await Db.InsertAsync<IT_MaterialBalance>(MaterialBalancelist);
        }

        /// <summary>
        /// 获取库存结存信息
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<MaterialBalance>> GetMaterialBalanceListAsync(PageInput<MaterialBalanceConditionDTO> input)
        {
            #region old
            var search = input.Search;
            string DWhere = "";
            if (!search.StartYearMonth.IsNullOrEmpty())
            {
                //DWhere = @" YearMonth='" + search.StartYearMonth + "' ";

                DateTime dt = Convert.ToDateTime(search.StartYearMonth);
                string startyearmonth = dt.AddMonths(-1).ToString("yyyy-MM");
                DWhere = @" YearMonth='" + startyearmonth + "' ";
            }
            else
            {
                DWhere = @" YearMonth='2022-01' ";
            }
            string InWhere = "";
            string OutWhere = "";
            if (!search.StartYearMonth.IsNullOrEmpty() && !search.EndYearMonth.IsNullOrEmpty())
            {
                string startdate = search.StartYearMonth + "-01";
                string endt = search.EndYearMonth + "-01";
                string enddate = Convert.ToDateTime(endt).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
                InWhere = @" where DATE_FORMAT(a.InStorTime,'%Y-%m-%d') BETWEEN '" + startdate + "' and '" + enddate + "' ";
                OutWhere = @" where DATE_FORMAT(a.OutTime,'%Y-%m-%d') BETWEEN '" + startdate + "' and '" + enddate + "' ";
            }
            string sql = @"select t.*,(t.LastMonth+t.InNums-t.OutNums) as ThisNums,e.`Name` as StorName from (
            select a.Id as MaterialId,a.`Code` as MaterialCode,a.`Name` as MaterialName,a.Spec as MaterialSpec,case when IFNULL(b.StorId,'')!='' then b.StorId when IFNULL(c.StorId,'')!='' then c.StorId when IFNULL(d.StorId,'')!='' then d.StorId end  StorId,IFNULL(b.LastMonth,0) as LastMonth,IFNULL(c.InNums,0) as InNums,IFNULL(d.OutNums,0) as OutNums
            from PB_Material as a
            left join (select StorId,MaterialId,LastMonth from IT_MaterialBalance WHERE {0} ) as b on a.Id=b.MaterialId
            left join (
            select b.StorId,b.MaterialId,SUM(b.Num) as InNums from TD_InStorage as a left join TD_InStorDetail as b on a.Id=b.InStorId {1} group by b.StorId,b.MaterialId
            ) as c on a.Id=c.MaterialId
            left join (
            select b.StorId,b.MaterialId,SUM(b.OutNum) as OutNums from TD_OutStorage as a left join TD_OutStorDetail as b on a.Id=b.OutStorId {2} group by b.StorId,b.MaterialId
            ) as d on a.Id=d.MaterialId
            )as t 
            left join Base_Department as e on t.StorId=e.Id
            where 1=1 ";
            if (search.MovablePin == true)
            {
                sql += @" and (t.LastMonth>0 or t.InNums>0 or t.OutNums>0) ";
            }
            if (!search.StorId.IsNullOrEmpty())
            {
                sql += @" AND t.StorId='" + search.StorId + "'";
            }
            if (!search.MaterialName.IsNullOrEmpty())
            {
                sql += @" AND (t.MaterialName like '%" + search.MaterialName + "%' or t.MaterialCode like '%" + search.MaterialName + "%')";
            }
            string sqlMain = string.Format(sql, DWhere, InWhere, OutWhere);
            var lidata = await Db.GetListBySqlAsync<MaterialBalance>(sqlMain);
            foreach (var item in lidata)
            {
                if (item.ThisNums < 0)
                {
                    item.LastMonth = item.OutNums;
                    item.ThisNums = item.LastMonth + item.InNums - item.OutNums;
                }
            }
            int num = lidata.Count;
            var list = lidata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            #endregion
            //#region new
            //var search = input.Search;
            ////获取基础数据
            //StringBuilder sql = new StringBuilder();
            //sql.Append("SELECT ");
            //sql.Append("ROW_NUMBER() over(order by StorName desc) as Rnumber,");
            //sql.Append("StorId,");
            //sql.Append("StorName,");
            //sql.Append("MaterialId,");
            //sql.Append("CODE MaterialCode,");
            //sql.Append("MaterialName,");
            //sql.Append("Spec MaterialSpec,");
            //sql.Append("0 LastMonth,");
            //sql.Append("sum( InNum ) InNums,");
            //sql.Append("sum( OutNum ) OutNums,");
            //sql.Append("0 ThisNums ");
            //sql.Append("FROM ");
            //sql.Append("V_InOutStorage ");
            //sql.Append("WHERE 1=1 ");
            //if (!search.StartYearMonth.IsNullOrEmpty() && !search.EndYearMonth.IsNullOrEmpty())
            //{
            //    sql.Append("AND CreateTime BETWEEN '" + search.StartYearMonth + "' AND '" + search.EndYearMonth + "' ");
            //}
            //else if (!search.StartYearMonth.IsNullOrEmpty() && search.EndYearMonth.IsNullOrEmpty())
            //{
            //    sql.Append("AND CreateTime >= '" + search.StartYearMonth + "' ");
            //}
            //else 
            //{
            //    sql.Append("AND CreateTime <= '" + search.StartYearMonth + "' ");
            //}
            //if (!search.StorId.IsNullOrEmpty())
            //{
            //    sql.Append("AND StorId='" + search.StorId + "' ");
            //}
            //if (!search.MaterialName.IsNullOrEmpty())
            //{
            //    sql.Append("AND (MaterialName like '%" + search.MaterialName + "%' or CODE like '%" + search.MaterialName + "%') ");
            //}
            //if (search.MovablePin == true)
            //{
            //    sql.Append("and (InNum>0 or OutNum>0) ");
            //}
            //sql.Append("GROUP BY ");
            //sql.Append("StorId,");
            //sql.Append("MaterialId,");
            //sql.Append("StorName,");
            //sql.Append("MaterialName,");
            //sql.Append("CODE,");
            //sql.Append("Spec ");
            //sql.Append("LIMIT ");
            //sql.Append((input.PageIndex-1)*input.PageRows);
            //sql.Append(","+input.PageRows);
            //var list = await Db.GetListBySqlAsync<MaterialBalance>(sql.ToString());

            //StringBuilder sqlCount=new StringBuilder();
            //sqlCount.Append("SELECT ");
            //sqlCount.Append("count( t.StorId ) ");
            //sqlCount.Append("FROM ");
            //sqlCount.Append("(SELECT ");
            //sqlCount.Append("StorId ");
            //sqlCount.Append("FROM ");
            //sqlCount.Append("V_InOutStorage ");
            //sqlCount.Append("WHERE 1=1 ");
            //if (!search.StartYearMonth.IsNullOrEmpty() && !search.EndYearMonth.IsNullOrEmpty())
            //{
            //    sqlCount.Append("AND CreateTime BETWEEN '" + search.StartYearMonth + "' AND '" + search.EndYearMonth + "' ");
            //}
            //else if (!search.StartYearMonth.IsNullOrEmpty() && search.EndYearMonth.IsNullOrEmpty())
            //{
            //    sqlCount.Append("AND CreateTime >= '" + search.StartYearMonth + "' ");
            //}
            //else
            //{
            //    sqlCount.Append("AND CreateTime <= '" + search.StartYearMonth + "' ");
            //}
            //if (!search.StorId.IsNullOrEmpty())
            //{
            //    sqlCount.Append("AND StorId='" + search.StorId + "' ");
            //}
            //if (!search.MaterialName.IsNullOrEmpty())
            //{
            //    sqlCount.Append("AND (MaterialName like '%" + search.MaterialName + "%' or CODE like '%" + search.MaterialName + "%') ");
            //}
            //if (search.MovablePin == true)
            //{
            //    sqlCount.Append("and (InNum>0 or OutNum>0) ");
            //}
            //sqlCount.Append("GROUP BY ");
            //sqlCount.Append("StorId,");
            //sqlCount.Append("MaterialId) t");
            //int num = Convert.ToInt32(Db.GetDataTableWithSql(sqlCount.ToString()).Rows[0][0].ToString());
            //string materialIds = "";
            //if (num > 0)
            //{
            //    foreach (var item in list)
            //    {
            //        materialIds += "'"+item.MaterialId+"',";
            //    }

            //    StringBuilder sqlMerge = new StringBuilder();
            //    sqlMerge.Append("SELECT ");
            //    sqlMerge.Append("StorId,");
            //    sqlMerge.Append("MaterialId,");
            //    sqlMerge.Append("sum( InNum )- sum( OutNum ) LastMonth ");
            //    sqlMerge.Append("FROM ");
            //    sqlMerge.Append("V_InOutStorage ");
            //    sqlMerge.Append("WHERE ");
            //    sqlMerge.Append("CreateTime<");
            //    sqlMerge.Append("'" + search.StartYearMonth + "' ");
            //    sqlMerge.Append("AND MaterialId IN (");
            //    sqlMerge.Append(materialIds.Substring(0, materialIds.Length-1));
            //    sqlMerge.Append(") ");
            //    sqlMerge.Append("GROUP BY ");
            //    sqlMerge.Append("StorId,");
            //    sqlMerge.Append("MaterialId");
            //    var lstMonth = await Db.GetListBySqlAsync<MaterialBalance>(sqlMerge.ToString());
            //    foreach (var itm in list)
            //    {
            //        var _lastmonth = lstMonth.FirstOrDefault(s => s.StorId.Equals(itm.StorId) && s.MaterialId.Equals(itm.MaterialId));
            //        itm.LastMonth = _lastmonth.IsNullOrEmpty() ? 0 : _lastmonth.LastMonth;
            //        itm.ThisNums = itm.LastMonth + itm.InNums - itm.OutNums;
            //        if (itm.ThisNums < 0)
            //        {
            //            itm.LastMonth = itm.OutNums;
            //            itm.ThisNums = itm.LastMonth + itm.InNums - itm.OutNums;
            //        }
            //    }
            //}
            //#endregion
            return new PageResult<MaterialBalance> { Data = list, Total = num };
        }

        /// <summary>
        /// 库存结存信息导出
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<MaterialBalance>> GetMaterialBalanceExcelAsync(MaterialBalanceConditionDTO input)
        {
            string DWhere = "";
            if (!input.StartYearMonth.IsNullOrEmpty())
            {
                DateTime dt = Convert.ToDateTime(input.StartYearMonth);
                string startyearmonth = dt.AddMonths(-1).ToString("yyyy-MM");
                DWhere = @" YearMonth='" + startyearmonth + "' ";
            }
            else
            {
                DWhere = @" YearMonth='2022-01' ";
            }
            string InWhere = "";
            string OutWhere = "";
            if (!input.StartYearMonth.IsNullOrEmpty() && !input.EndYearMonth.IsNullOrEmpty())
            {
                string startdate = input.StartYearMonth + "-01";
                string endt = input.EndYearMonth + "-01";
                string enddate = Convert.ToDateTime(endt).AddMonths(1).AddDays(-1).ToString("yyyy-MM-dd");
                InWhere = @" where DATE_FORMAT(a.InStorTime,'%Y-%m-%d') BETWEEN '" + startdate + "' and '" + enddate + "' ";
                OutWhere = @" where DATE_FORMAT(a.OutTime,'%Y-%m-%d') BETWEEN '" + startdate + "' and '" + enddate + "' ";
            }
            string sql = @"select t.*,(t.LastMonth+t.InNums-t.OutNums) as ThisNums,e.`Name` as StorName from (
select a.Id as MaterialId,a.`Code` as MaterialCode,a.`Name` as MaterialName,a.Spec as MaterialSpec,case when IFNULL(b.StorId,'')!='' then b.StorId when IFNULL(c.StorId,'')!='' then c.StorId when IFNULL(d.StorId,'')!='' then d.StorId end  StorId,IFNULL(b.LastMonth,0) as LastMonth,IFNULL(c.InNums,0) as InNums,IFNULL(d.OutNums,0) as OutNums
from PB_Material as a
left join (select StorId,MaterialId,LastMonth from IT_MaterialBalance WHERE {0} ) as b on a.Id=b.MaterialId
left join (
select b.StorId,b.MaterialId,SUM(b.Num) as InNums from TD_InStorage as a left join TD_InStorDetail as b on a.Id=b.InStorId {1} group by b.StorId,b.MaterialId
) as c on a.Id=c.MaterialId
left join (
select b.StorId,b.MaterialId,SUM(b.OutNum) as OutNums from TD_OutStorage as a left join TD_OutStorDetail as b on a.Id=b.OutStorId {2} group by b.StorId,b.MaterialId
) as d on a.Id=d.MaterialId
)as t 
left join Base_Department as e on t.StorId=e.Id
where 1=1 ";
            if (input.MovablePin == true)
            {
                sql += @" and (t.LastMonth>0 or t.InNums>0 or t.OutNums>0) ";
            }
            if (!input.StorId.IsNullOrEmpty())
            {
                sql += @" AND t.StorId='" + input.StorId + "'";
            }
            if (!input.MaterialName.IsNullOrEmpty())
            {
                sql += @" AND (t.MaterialName like '%" + input.MaterialName + "%' or t.MaterialCode like '%" + input.MaterialName + "%')";
            }
            string sqlMain = string.Format(sql, DWhere, InWhere, OutWhere);
            List<MaterialBalance> listdata = await Db.GetListBySqlAsync<MaterialBalance>(sqlMain);
            foreach (var item in listdata)
            {
                if (item.ThisNums < 0)
                {
                    item.LastMonth = item.OutNums;
                    item.ThisNums = item.LastMonth + item.InNums - item.OutNums;
                }
            }
            return listdata;
        }

        /// <summary>
        /// 获取结存年份
        /// </summary>
        /// <returns></returns>
        public async Task<List<string>> GetYearAsync()
        {
            //获取操作人的所有仓库
            List<string> liststor = await _opdept.GetStorList(_operator.Property?.FactoryId);
            string storids = string.Join(",", liststor.ToArray()).Replace(",", "','");
            string sqlMain = @"select YEAR(InStorTime) as `Year` from TD_InStorage where StorId in ('" + storids + "') group by YEAR(InStorTime)";
            var listdata = await Db.GetDataTableWithSqlAsync(sqlMain);
            List<string> listyear = new List<string>();
            foreach (DataRow dr in listdata.Rows)
            {
                listyear.Add(dr["Year"].ToString());
            }
            return listyear;
        }

        /// <summary>
        /// 获取年月下的月分结存信息
        /// </summary>
        /// <param name="year">年份</param>
        /// <returns></returns>
        public async Task<List<YearMonthDTO>> GetYearMonthBalanceAsync(string year)
        {
            //获取年份
            int YearName = DateTime.Now.Year;

            //获取操作人的所有仓库
            List<string> liststor = await _opdept.GetStorList(_operator.Property?.FactoryId);
            string storids = string.Join(",", liststor.ToArray()).Replace(",", "','");
            string sqlMain = @"select DATE_FORMAT(InStorTime,'%Y-%m') as YearMonth from TD_InStorage where StorId in ('" + storids + "') AND YEAR(InStorTime)='" + year + "' ";
            if (Convert.ToInt32(year) == YearName)
            {
                string dttime = DateTime.Now.AddDays(1 - DateTime.Now.Day).Date.ToString("yyyy-MM-dd");
                sqlMain += @" and InStorTime<'" + dttime + "' ";
            }
            sqlMain += @" group by DATE_FORMAT(InStorTime,'%Y-%m')  order by DATE_FORMAT(InStorTime,'%Y-%m')";
            var listdata = await Db.GetDataTableWithSqlAsync(sqlMain);
            string itemsql = @" select YearMonth,case when Nums>0 THEN 1 else 0 end as `Status`,TrueOrFalse from ( ";

            for (int i = 0; i < listdata.Rows.Count; i++)
            {
                string str = listdata.Rows[i][0].ToString();
                if (i == 0)
                {
                    itemsql += @" select '" + listdata.Rows[i][0].ToString() + "' as YearMonth,(select Count(*) as Nums from IT_MaterialBalance where YearMonth='" + listdata.Rows[i][0].ToString() + "' and StorId in('" + storids + "')) as Nums,'0' as TrueOrFalse ";
                }
                else
                {
                    itemsql += @" UNION ALL select '" + listdata.Rows[i][0].ToString() + "' as YearMonth,(select Count(*) as Nums from IT_MaterialBalance where YearMonth='" + listdata.Rows[i][0].ToString() + "' and StorId in('" + storids + "')) as Nums,'0' as TrueOrFalse ";
                }
            }
            if (Convert.ToInt32(year) == YearName)
            {
                int month = DateTime.Now.Month;
                string monthname = month < 10 ? ("0" + month).ToString() : month.ToString();
                string yearmonth = year + "-" + monthname;
                itemsql += @" UNION ALL select '" + yearmonth + "' as YearMonth,'0' as Nums,'0' as TrueOrFalse ";
            }
            itemsql += @") as t";
            List<YearMonthDTO> listYearMonth = await Db.GetListBySqlAsync<YearMonthDTO>(itemsql);
            //重置结存最后一个月的回滚状态
            var model = listYearMonth.Where(x => x.Status == 1).OrderByDescending(x => x.YearMonth).FirstOrDefault();
            if (model != null)
            {
                model.TrueOrFalse = 1;
            }
            return listYearMonth;
        }

        /// <summary>
        /// 获取结存详情
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResult<MaterialBalance>> GetYearMonthBalanceListAsync(PageInput<BalanceConditionDTO> input)
        {
            var search = input.Search;
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(" SELECT ROW_NUMBER()over(order by a.MaterialId) as Rnumber,a.StorId,b.`Name` as StorName,a.MaterialId,c.`Code` as MaterialCode,c.`Name` as MaterialName,c.Spec as MaterialSpec,a.LastMonth,a.InNums,a.OutNums,a.ThisNums ");
            sqlStr.Append(" FROM IT_MaterialBalance as a ");
            sqlStr.Append(" join Base_Department as b on a.StorId=b.Id ");
            sqlStr.Append(" join PB_Material as c on a.MaterialId=c.Id ");
            sqlStr.Append(" where a.YearMonth='" + search.yearmonth + "' ");
            if (!search.Keyword.IsNullOrEmpty())
            {
                sqlStr.Append(" and (c.`Code` like '%"+ search.Keyword + "%' or c.`Name` like '%" + search.Keyword + "%') ");
            }
            if (!search.StorId.IsNullOrEmpty())
            {
                sqlStr.Append(" and a.StorId='" + search.StorId + "' ");
            }
            List<MaterialBalance> listdata = await Db.GetListBySqlAsync<MaterialBalance>(sqlStr.ToString());
            foreach (var item in listdata)
            {
                if (item.ThisNums < 0)
                {
                    item.LastMonth = item.OutNums;
                    item.ThisNums = item.LastMonth + item.InNums - item.OutNums;
                }
            }
            int num = listdata.Count;
            var list = listdata
               .Skip((input.PageIndex - 1) * input.PageRows)
               .Take(input.PageRows).ToList();
            return new PageResult<MaterialBalance> { Data = list, Total = num };
        }

        /// <summary>
        /// 结存回滚
        /// </summary>
        /// <param name="yearmonth">年月</param>
        /// <returns></returns>
        public async Task UpRollBACKAsync(string yearmonth)
        {
            string sqlMain = @"DELETE from IT_MaterialBalance where YearMonth='"+ yearmonth + "'";
            await ExecuteSqlAsync(sqlMain);
        }

        /// <summary>
        /// 本月结存
        /// </summary>
        /// <param name="yearmonth">年月</param>
        /// <returns></returns>
        public async Task UpBalanceAsync(string yearmonth)
        {
            DateTime dt = Convert.ToDateTime(yearmonth + "-01");

            //本月月初
            DateTime startime = dt;
            //本月月末
            DateTime endtime = dt.AddMonths(1).AddSeconds(-1);

            //获取登录人所属工厂下的所有仓库
            List<string> StorIds = await _opdept.GetStorString();

            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();

            List<StorMaterialDTO> listmaterial = new List<StorMaterialDTO>();
            //入库情况
            Expression<Func<TD_InStorDetail, TD_InStorage, InStorDTO>> select = (a, b) => new InStorDTO
            {
                Year = b.InStorTime.Year,
                Month = b.InStorTime.Month,
                InStorTime = b.InStorTime,
                StorId = a.StorId,
                MaterialId = a.MaterialId,
                Num = a.Num
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<TD_InStorDetail>().AsExpandable()
                    join b in Db.GetIQueryable<TD_InStorage>() on a.InStorId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    select @select.Invoke(a, b);
            var Inlist = await q.Where(x => StorIds.Contains(x.StorId) && x.InStorTime >= startime && x.InStorTime <= endtime).ToListAsync();
            var instor = Inlist.GroupBy(x => new { x.Year, x.Month, x.StorId, x.MaterialId }).Select(x => new { Year = x.Key.Year, Month = x.Key.Month, StorId = x.Key.StorId, MaterialId = x.Key.MaterialId, Nums = x.Sum(j => j.Num) }).ToList();
            foreach (var item in instor)
            {
                StorMaterialDTO smdata = new StorMaterialDTO();
                smdata.StorId = item.StorId;
                smdata.MaterialId = item.MaterialId;
                listmaterial.Add(smdata);
            }

            //出库情况
            Expression<Func<TD_OutStorDetail, TD_OutStorage, OutStorDTO>> selectout = (a, b) => new OutStorDTO
            {
                Year = b.OutTime.Year,
                Month = b.OutTime.Month,
                OutTime = b.OutTime,
                StorId = a.StorId,
                MaterialId = a.MaterialId,
                OutNum = a.OutNum
            };
            selectout = selectout.BuildExtendSelectExpre();
            var qout = from a in Db.GetIQueryable<TD_OutStorDetail>().AsExpandable()
                       join b in Db.GetIQueryable<TD_OutStorage>() on a.OutStorId equals b.Id into ab
                       from b in ab.DefaultIfEmpty()
                       select @selectout.Invoke(a, b);
            var Outlist = await qout.Where(x => StorIds.Contains(x.StorId) && x.OutTime >= startime && x.OutTime <= endtime).ToListAsync();
            var outstor = Outlist.GroupBy(x => new { x.Year, x.Month, x.StorId, x.MaterialId }).Select(x => new { Year = x.Key.Year, Month = x.Key.Month, StorId = x.Key.StorId, MaterialId = x.Key.MaterialId, Nums = x.Sum(j => j.OutNum) }).ToList();
            foreach (var item in outstor)
            {
                StorMaterialDTO smdata = new StorMaterialDTO();
                smdata.StorId = item.StorId;
                smdata.MaterialId = item.MaterialId;
                listmaterial.Add(smdata);
            }

            //查询当前月的上月结转信息
            List<IT_MaterialBalance> listdata = await Db.GetIQueryable<IT_MaterialBalance>().Where(x => x.YearMonth == startime.AddMonths(-1).ToString("yyyy-MM")).ToListAsync();

            List<IT_MaterialBalance> MaterialBalancelist = new List<IT_MaterialBalance>();
            //当前月的出入库物料
            var StorMdata = listmaterial.GroupBy(x => new { x.StorId, x.MaterialId }).Select(x => new { StorId = x.Key.StorId, MaterialId = x.Key.MaterialId }).ToList();

            //先循环插入本月出入库的结转信息
            foreach (var item in StorMdata)
            {
                IT_MaterialBalance mbdata = new IT_MaterialBalance();
                mbdata.Id = IdHelper.GetId();
                mbdata.Year = startime.Year.ToString();
                mbdata.Month = startime.Month < 10 ? ("0" + startime.Month) : startime.Month.ToString();
                mbdata.YearMonth = startime.ToString("yyyy-MM");
                mbdata.StorId = item.StorId;
                mbdata.MaterialId = item.MaterialId;
                //查询上月结转信息是否存在
                var lastmonthdata = listdata.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                if (lastmonthdata != null)
                {
                    mbdata.LastMonth = lastmonthdata.ThisNums;
                }
                else
                {
                    mbdata.LastMonth = 0;
                }
                //查询本月入库信息
                var initem = instor.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                if (initem != null)
                {
                    mbdata.InNums = initem.Nums;
                }
                else
                {
                    mbdata.InNums = 0;
                }
                //查询本月出库信息
                var outitem = outstor.Where(x => x.StorId == item.StorId && x.MaterialId == item.MaterialId).FirstOrDefault();
                if (outitem != null)
                {
                    mbdata.OutNums = outitem.Nums;
                }
                else
                {
                    mbdata.OutNums = 0;
                }
                mbdata.ThisNums = (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums) < 0 ? 0 : (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums);
                mbdata.CreatorId = _operator.UserId;
                mbdata.CreatorRealName = userdata.RealName;
                mbdata.CreateTime = DateTime.Now;
                mbdata.Deleted = false;
                MaterialBalancelist.Add(mbdata);
            }
            List<IT_MaterialBalance> MBlist = new List<IT_MaterialBalance>();
            foreach (var item in listdata)
            {
                var itemdata = StorMdata.Where(x => x.MaterialId == item.MaterialId && x.StorId == item.StorId).FirstOrDefault();
                if (itemdata == null)
                {
                    MBlist.Add(item);
                }
            }
            foreach (var item in MBlist)
            {
                IT_MaterialBalance mbdata = new IT_MaterialBalance();
                mbdata.Id = IdHelper.GetId();
                mbdata.Year = startime.Year.ToString();
                mbdata.Month = startime.Month < 10 ? ("0" + startime.Month) : startime.Month.ToString();
                mbdata.YearMonth = startime.ToString("yyyy-MM");
                mbdata.StorId = item.StorId;
                mbdata.MaterialId = item.MaterialId;
                mbdata.LastMonth = item.ThisNums;
                mbdata.InNums = 0;
                mbdata.OutNums = 0;
                mbdata.ThisNums = (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums) < 0 ? 0 : (mbdata.LastMonth + mbdata.InNums - mbdata.OutNums);
                mbdata.CreatorId = _operator.UserId;
                mbdata.CreatorRealName = userdata.RealName;
                mbdata.CreateTime = DateTime.Now;
                mbdata.Deleted = false;
                MaterialBalancelist.Add(mbdata);
            }

            await Db.InsertAsync<IT_MaterialBalance>(MaterialBalancelist);
        }

        /// <summary>
        /// 获取总总资产统计数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResultData<IT_TotalAssetsDto>> GetTotalAssetsList(PageInput<IT_LocalDetailDtolistContionDTO> input)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine(" SELECT");


     sql.AppendLine("T.Id,");

            sql.AppendLine(" T.`Code` 'MaterialCode',");

            sql.AppendLine("T.`Name` 'MaterialName',");

            sql.AppendLine(" T.Weight,");

            sql.AppendLine(" T.IsProduct,");

            sql.AppendLine("  T.ProductCode,");

            sql.AppendLine(" T.Spec,");

            sql.AppendLine("IFNULL(IT_LocalMaterial.MaterialwarehouseNums, 0) 'MaterialwarehouseNums',");

            sql.AppendLine("round(IFNULL(IT_LocalMaterial.MaterialwarehouseWeight / 1000, 0), 4) 'MaterialwarehouseWeight',");


            sql.AppendLine("round(IFNULL(Inproject.MaterialOfWeight / 1000, 0), 4) 'MaterialOfWeight',");

            sql.AppendLine("IFNULL(Inproject.MaterialOfNums, 0) 'MaterialOfNums',");


            sql.AppendLine("IFNULL(OntheWay.MaterialtransitNums, 0) 'MaterialtransitNums',");

            sql.AppendLine("round(IFNULL(OntheWay.MaterialtransitWeight / 1000, 0), 4) 'MaterialtransitWeight',");


            sql.AppendLine("IFNULL(Toberenovated.ToberenovatedNums, 0) 'ToberenovatedNums',");

            sql.AppendLine("ROUND(IFNULL(Toberenovated.ToberenovatedWeight, 0) / 1000, 4) 'ToberenovatedWeight',");


            sql.AppendLine("IFNULL(IT_LocalMaterial.MaterialwarehouseNums + Inproject.MaterialOfNums + OntheWay.MaterialtransitNums +Toberenovated.ToberenovatedNums, 0) 'TotalQuantity',");

      sql.AppendLine("ROUND((");
             sql.AppendLine(" IFNULL(IT_LocalMaterial.MaterialwarehouseWeight, 0) + IFNULL(OntheWay.MaterialtransitWeight, 0) + IFNULL (OntheWay.MaterialtransitWeight, 0) + IFNULL(Toberenovated.ToberenovatedWeight, 0)) / 1000,");
        sql.AppendLine("  4");
      sql.AppendLine(") 'TotalWeight'");
            sql.AppendLine("FROM");
            sql.AppendLine(" (SELECT Id, `Name`, `Code`, Weight, IsProduct, ProductCode, Spec FROM PB_Material) AS T");

            sql.AppendLine("LEFT JOIN(");
            sql.AppendLine(" SELECT");
            sql.AppendLine("  MaterialId,");
            sql.AppendLine(" IFNULL(SUM(Nums_N +Nums_O ) *PB_Material.Weight, 0 ) AS MaterialwarehouseWeight,");
            sql.AppendLine("IFNULL( SUM(Nums_N + Nums_O), 0 ) AS MaterialwarehouseNums,");
            sql.AppendLine(" PB_Material.IsProduct");
            sql.AppendLine("FROM");

            sql.AppendLine(" IT_LocalMaterial");
            sql.AppendLine(" LEFT JOIN PB_Material ON IT_LocalMaterial.MaterialId = PB_Material.Id");

            sql.AppendLine(" LEFT JOIN Base_Department ON IT_LocalMaterial.StorId = Base_Department.Id");

            sql.AppendLine(" AND Base_Department.Type = 2");

            sql.AppendLine(" WHERE");

            sql.AppendLine(" Base_Department.`Name` NOT LIKE '%回收库%'");

            sql.AppendLine(" GROUP BY");

            sql.AppendLine("  IT_LocalMaterial.MaterialId");

            sql.AppendLine(") AS IT_LocalMaterial ON IT_LocalMaterial.MaterialId = T.Id");


            sql.AppendLine(" LEFT JOIN(");
            sql.AppendLine(" SELECT");
            sql.AppendLine(" MaterialId,");
            sql.AppendLine(" IFNULL(SUM(Nums_N +Nums_O ) *PB_Material.Weight, 0 ) AS ToberenovatedWeight,");
            sql.AppendLine("IFNULL( SUM(Nums_N + Nums_O), 0 ) AS ToberenovatedNums,");
            sql.AppendLine("PB_Material.IsProduct,");

            sql.AppendLine(" Base_Department.`Name` ");

            sql.AppendLine("FROM");
            sql.AppendLine("  IT_LocalMaterial");

            sql.AppendLine("  LEFT JOIN PB_Material ON IT_LocalMaterial.MaterialId = PB_Material.Id");

            sql.AppendLine(" LEFT JOIN Base_Department ON IT_LocalMaterial.StorId = Base_Department.Id");

            sql.AppendLine(" AND Base_Department.Type = 2");

            sql.AppendLine("WHERE");

            sql.AppendLine(" Base_Department.`Name` LIKE '%回收库%'");

            sql.AppendLine(" GROUP BY");

            sql.AppendLine(" IT_LocalMaterial.MaterialId,");

            sql.AppendLine("Base_Department.`Name` ");

            sql.AppendLine(") AS Toberenovated ON Toberenovated.MaterialId = T.Id");

            sql.AppendLine(" LEFT JOIN(");
            sql.AppendLine("   SELECT");
            sql.AppendLine("  IFNULL(sum(m.ReceiptQuantity* t.Weight ), 0 ) MaterialOfWeight,");

            sql.AppendLine("sum(m.ReceiptQuantity) MaterialOfNums,");

            sql.AppendLine("m.MaterialId MaterialId,");
            sql.AppendLine("  t.IsProduct,");

            sql.AppendLine("IFNULL(t.Weight, 0) Weight");
            sql.AppendLine(" FROM");

            sql.AppendLine(" PB_TransportBillDetail m");

            sql.AppendLine(" LEFT JOIN PB_Material t ON m.MaterialId = t.Id");

            sql.AppendLine(" LEFT JOIN PB_TransportBill bill ON m.ParentId = bill.Id");

            sql.AppendLine("  AND bill.`State` > 4");

            sql.AppendLine(" GROUP BY");

            sql.AppendLine(" m.MaterialId");
            sql.AppendLine("	) AS Inproject ON Inproject.MaterialId = T.Id");

            sql.AppendLine(" LEFT JOIN(");
            sql.AppendLine(" SELECT");
            sql.AppendLine(" IFNULL(sum(m.PackNums* t.Weight ), 0 ) MaterialtransitWeight,");

            sql.AppendLine("	sum(m.PackNums) MaterialtransitNums,");

            sql.AppendLine("m.MaterialId MaterialId,");
            sql.AppendLine("  t.IsProduct,");

            sql.AppendLine("IFNULL(t.Weight, 0) Weight");
            sql.AppendLine("FROM");

            sql.AppendLine(" PB_TransportBillDetail m");

            sql.AppendLine("  LEFT JOIN PB_Material t ON m.MaterialId = t.Id");

            sql.AppendLine("   LEFT JOIN PB_TransportBill bill ON m.ParentId = bill.Id");

            sql.AppendLine("  AND bill.`State` = 4");

            sql.AppendLine(" GROUP BY");

            sql.AppendLine("  m.MaterialId");

            sql.AppendLine(") AS OntheWay ON OntheWay.MaterialId = T.Id");
            sql.AppendLine("WHERE");

            sql.AppendLine("T.IsProduct = TRUE");
            if (!input.Search.MaterialName.IsNullOrEmpty())
            {
                sql.AppendLine("AND T.Name LIKE '%" + input.Search.MaterialName + "%' OR T.ProductCode LIKE '%" + input.Search.MaterialName + "%' ");
            }
            sql.AppendLine("And(  IT_LocalMaterial.MaterialwarehouseNums > 0");

            sql.AppendLine(" OR Inproject.MaterialOfNums > 0");
            sql.AppendLine("OR OntheWay.MaterialtransitNums > 0)");
           
            var res = await Db.GetListBySqlAsync<IT_TotalAssetsDto>(sql.ToString());
            int pagecount;//总页数
            List<IT_TotalAssetsDto> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<IT_TotalAssetsDto> productes = new List<IT_TotalAssetsDto>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            StringBuilder sqllist = new StringBuilder();
            sqllist.AppendLine(" SELECT");
            sql.AppendLine("   T.IsProduct,");
            sql.AppendLine("   T.ProductCode,");
            sqllist.AppendLine("IFNULL(SUM(IT_LocalMaterial.MaterialwarehouseNums) + SUM(Inproject.MaterialOfNums) + SUM(OntheWay.MaterialtransitNums), 0) 'TotalQuantity' ,");
            sqllist.AppendLine("  ROUND( IFNULL(Sum(IT_LocalMaterial.MaterialwarehouseWeight) + SUM(Inproject.MaterialOfWeight) + SUM(OntheWay.MaterialtransitWeight), 0)/1000,4) 'TotalWeight'");
            sqllist.AppendLine("FROM");
            sqllist.AppendLine("(SELECT Id, `Name`, `Code`, Weight,IsProduct,ProductCode FROM PB_Material) AS T ");
            sqllist.AppendLine("LEFT JOIN(");
            sqllist.AppendLine("SELECT");
            sqllist.AppendLine("MaterialId,");
            sqllist.AppendLine("PB_Material.IsProduct,");
            sqllist.AppendLine("IFNULL(SUM(Nums_N + Nums_O) * PB_Material.Weight, 0) AS MaterialwarehouseWeight,");
            sqllist.AppendLine("IFNULL(SUM(Nums_N + Nums_O), 0) AS MaterialwarehouseNums");
            sqllist.AppendLine("FROM");
            sqllist.AppendLine("IT_LocalMaterial");
            sqllist.AppendLine("LEFT JOIN PB_Material ON IT_LocalMaterial.MaterialId = PB_Material.Id");
            sqllist.AppendLine("GROUP BY");
            sqllist.AppendLine("IT_LocalMaterial.MaterialId");
            sqllist.AppendLine(") AS IT_LocalMaterial ON IT_LocalMaterial.MaterialId = T.Id");
            sqllist.AppendLine("LEFT JOIN(");
            sqllist.AppendLine("SELECT");
            sqllist.AppendLine("IFNULL(sum(m.ReceiptQuantity* t.Weight ), 0 ) MaterialOfWeight,");
            sqllist.AppendLine("sum(m.ReceiptQuantity) MaterialOfNums,");
            sqllist.AppendLine("m.MaterialId MaterialId,");
            sqllist.AppendLine(" t.IsProduct,");
            sqllist.AppendLine("IFNULL( t.Weight, 0 ) Weight");
            sqllist.AppendLine("FROM");
            sqllist.AppendLine("PB_TransportBillDetail m");
            sqllist.AppendLine("LEFT JOIN PB_Material t ON m.MaterialId = t.Id");
            sqllist.AppendLine("LEFT JOIN PB_TransportBill bill ON m.ParentId = bill.Id");
            sqllist.AppendLine("AND bill.`State` > 4");
            sqllist.AppendLine("GROUP BY");
            sqllist.AppendLine("m.MaterialId");
            sqllist.AppendLine(") AS Inproject ON Inproject.MaterialId = T.Id");
            sqllist.AppendLine("LEFT JOIN(");
            sqllist.AppendLine("SELECT");
            sqllist.AppendLine("IFNULL(sum(m.PackNums* t.Weight ), 0 ) MaterialtransitWeight,");
            sqllist.AppendLine("sum(m.PackNums) MaterialtransitNums,");
            sqllist.AppendLine("m.MaterialId MaterialId,");
            sqllist.AppendLine(" t.IsProduct,");
            sqllist.AppendLine("IFNULL( t.Weight, 0 ) Weight");
            sqllist.AppendLine("FROM");
            sqllist.AppendLine("PB_TransportBillDetail m");
            sqllist.AppendLine("LEFT JOIN PB_Material t ON m.MaterialId = t.Id");
            sqllist.AppendLine("LEFT JOIN PB_TransportBill bill ON m.ParentId = bill.Id");
            sqllist.AppendLine("AND bill.`State` = 4");
            sqllist.AppendLine("GROUP BY");
            sqllist.AppendLine("m.MaterialId");
            sqllist.AppendLine(" ) AS OntheWay ON OntheWay.MaterialId = T.Id");
            sqllist.AppendLine("WHERE");
            sqllist.AppendLine(" T.IsProduct = TRUE");
            if (!input.Search.MaterialName.IsNullOrEmpty())
            {
                sqllist.AppendLine("AND  T.Name LIKE '%" + input.Search.MaterialName + "%' OR T.ProductCode LIKE '%" + input.Search.MaterialName + "%' ");
            }
            var list =await Db.GetListBySqlAsync<PageResultDataList>(sqllist.ToString()); 
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResultData<IT_TotalAssetsDto> { Data = productes, Total = pagees,TotalQuantity=list[0].TotalQuantity,TotalWeight=list[0].TotalWeight };
        }
        /// <summary>
        /// 获取项目汇总资产数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResultData<PB_ProjectAssets>> ProjectAssetsList(PageInput<IT_LocalDetailDtolistContionDTO> input)
        {
            StringBuilder sql = new StringBuilder();
             sql.AppendLine(" -- 项目资产统计 ");
             sql.AppendLine("SELECT ");
             sql.AppendLine("ProjectInfo.*, ");
             sql.AppendLine("Shippeddata.ShippedNums, ");
             sql.AppendLine("ROUND(Shippeddata.ShippedWeight / 1000, 4) 'ShippedWeight' ");
             sql.AppendLine("FROM ");
             sql.AppendLine("( ");
             sql.AppendLine("SELECT ");
             sql.AppendLine("project.Id 'Id', ");
             sql.AppendLine("project.`Name` 'Name', ");
             sql.AppendLine("IFNULL(sum(StandsNums + 0), 0) 'StandsNums', ");
             sql.AppendLine("IFNULL(Customer.`Name`, '无') AS 'PurchasingSelling' ");
             sql.AppendLine("FROM ");
             sql.AppendLine("PB_DesignList AS pd_designList ");
             sql.AppendLine("LEFT JOIN PB_Project AS project ON pd_designList.ProjectId = project.Id ");
             sql.AppendLine("LEFT JOIN PB_Customer AS Customer ON project.Purchaseandsalesunit = Customer.Id ");
             sql.AppendLine("GROUP BY ");
             sql.AppendLine("pd_designList.ProjectId ");
             sql.AppendLine(") AS ProjectInfo ");
             sql.AppendLine("LEFT JOIN( ");
             sql.AppendLine("SELECT ");
             sql.AppendLine("IFNULL(sum(C.OutNum ), 0 ) ShippedNums, ");
             sql.AppendLine("IFNULL(sum(C.OutNum * M.Weight), 0) ShippedWeight, ");
             sql.AppendLine("A.ProjectId ");
             sql.AppendLine("FROM ");
             sql.AppendLine("PB_DesignList AS A ");
             sql.AppendLine("LEFT JOIN PB_Buildingnumber AS bu ON A.BuildingNumberID = bu.Id ");
             sql.AppendLine("LEFT JOIN PB_DesignDetail AS F ON F.ParentId = A.Id ");
             sql.AppendLine("LEFT JOIN PB_PickUpList e ON F.Id = e.SubId ");
             sql.AppendLine("AND e.MaterialId = F.MaterialId ");
             sql.AppendLine("LEFT JOIN PB_Material AS M ON F.MaterialId = M.Id ");
             sql.AppendLine("LEFT JOIN TD_OutStorage AS TD_OutStorage ON e.ParentId = TD_OutStorage.RefCode ");
             sql.AppendLine("LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId ");
             sql.AppendLine("AND C.MaterialId = F.MaterialId ");
             sql.AppendLine("GROUP BY ");
            sql.AppendLine("A.ProjectId ");
            sql.AppendLine(") AS Shippeddata ON Shippeddata.ProjectId = ProjectInfo.Id ");
            if (!input.Search.MaterialName.IsNullOrEmpty()) {
                sql.AppendLine("WHERE  ProjectInfo.`Name` LIKE '%" + input.Search.MaterialName+"%'");
            }
           var res  =await Db.GetListBySqlAsync<PB_ProjectAssets>(sql.ToString());
            int pagecount;
            List<PB_ProjectAssets> listpro_down = res.ToList();
            List<PB_ProjectAssets> productes = new List<PB_ProjectAssets>();
            int pagees = listpro_down.Count;
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            #region

            var lis =await DDD(input.Search.MaterialName);
            #endregion
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();
            return new PageResultData<PB_ProjectAssets> { Data = productes, Total = pagees,ToStandsNums=lis[0].ToStandsNums,TotalWeight=lis[0].TotalWeight,TotalQuantity=lis[0].TotalQuantity };
        }

        public async Task< List<PageResultDataList>>DDD(String MaterialName)
        {
            StringBuilder sqlmain = new StringBuilder();
            sqlmain.AppendLine("SELECT");
            sqlmain.AppendLine("IFNULL(SUM(ProjectInfo.StandsNums), 0) 'ToStandsNums',");
            sqlmain.AppendLine("IFNULL(Sum(Shippeddata.ShippedNums), 0) 'TotalQuantity',");
            sqlmain.AppendLine("round(IFNULL(SUM(Shippeddata.ShippedWeight), 0)/1000, 4) 'TotalWeight'");
            sqlmain.AppendLine("FROM");
            sqlmain.AppendLine("(");
            sqlmain.AppendLine("SELECT");
            sqlmain.AppendLine("project.Id 'Id',");
            sqlmain.AppendLine("project.`Name` 'Name',");
            sqlmain.AppendLine("IFNULL(sum(StandsNums + 0), 0) 'StandsNums',");
            sqlmain.AppendLine("IFNULL(Customer.`Name`, '无') AS 'PurchasingSelling'");
            sqlmain.AppendLine("FROM");
            sqlmain.AppendLine("PB_DesignList AS pd_designList");
            sqlmain.AppendLine("LEFT JOIN PB_Project AS project ON pd_designList.ProjectId = project.Id");
            sqlmain.AppendLine("LEFT JOIN PB_Customer AS Customer ON project.Purchaseandsalesunit = Customer.Id");
            sqlmain.AppendLine("GROUP BY");
            sqlmain.AppendLine("pd_designList.ProjectId");
            sqlmain.AppendLine(" ) AS ProjectInfo");
            sqlmain.AppendLine("LEFT JOIN(");
            sqlmain.AppendLine("SELECT");
            sqlmain.AppendLine("IFNULL(sum(C.OutNum ), 0 ) ShippedNums,");
            sqlmain.AppendLine("IFNULL(sum(C.OutNum * M.Weight), 0) ShippedWeight,");
            sqlmain.AppendLine("A.ProjectId");
            sqlmain.AppendLine("FROM");
            sqlmain.AppendLine("PB_DesignList AS A");
            sqlmain.AppendLine("LEFT JOIN PB_Buildingnumber AS bu ON A.BuildingNumberID = bu.Id");
            sqlmain.AppendLine("LEFT JOIN PB_DesignDetail AS F ON F.ParentId = A.Id");
            sqlmain.AppendLine("LEFT JOIN PB_PickUpList e ON F.Id = e.SubId");
            sqlmain.AppendLine("AND e.MaterialId = F.MaterialId");
            sqlmain.AppendLine("LEFT JOIN PB_Material AS M ON F.MaterialId = M.Id");
            sqlmain.AppendLine("LEFT JOIN TD_OutStorage AS TD_OutStorage ON e.ParentId = TD_OutStorage.RefCode");
            sqlmain.AppendLine("LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId");
            sqlmain.AppendLine("AND C.MaterialId = F.MaterialId");
            sqlmain.AppendLine("GROUP BY");
            sqlmain.AppendLine("A.ProjectId");
            sqlmain.AppendLine(") AS Shippeddata ON Shippeddata.ProjectId = ProjectInfo.Id");
            if (!MaterialName.IsNullOrEmpty())
            {
                sqlmain.AppendLine("WHERE  ProjectInfo.`Name` LIKE '%" +MaterialName + "%'");
            }
            var lis = await Db.GetListBySqlAsync<PageResultDataList>(sqlmain.ToString());
            return lis;
        }

        /// <summary>
        /// 项目报损统计
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PageResultData<IT_ProjectLosses>> IGetProjectLosses(PageInput<IT_LocalDetailDtolistContionDTO> input)
        {
            StringBuilder sql = new StringBuilder();

           sql.AppendLine("  SELECT ");

            sql.AppendLine("    OK.ProjectId,");

            sql.AppendLine("     OK.ProjectName,");

            sql.AppendLine("    OK.MaterialName,");
            sql.AppendLine("    OK.MaterialId,");

            sql.AppendLine("    ROUND(OK.RealNumsWeight / 1000, 4) 'RealNumsWeight' ,");


            sql.AppendLine("   PB_PickUpList.IssuedQuantity,");

            sql.AppendLine("    ROUND(PB_PickUpList.IssuedQuantityWeight / 1000, 4) 'IssuedQuantityWeight',");

            sql.AppendLine("    OK.MaterialCode,");


            sql.AppendLine("   IFNULL(Returnlog.ThisReturnNums, 0)  ThisReturnNums ,");

            sql.AppendLine("  IFNULL(ROUND(Returnlog.ThisReturnWeight, 4), 0)        'ThisReturnWeight',");


            sql.AppendLine("   IFNULL(list.`Name`, '') CustomerName");
            sql.AppendLine("     FROM");
            sql.AppendLine("    (");
            sql.AppendLine("    SELECT");


            sql.AppendLine("   MaterialId AS 'MaterialId',");
            sql.AppendLine("     MaterialName AS 'MaterialName',");
            sql.AppendLine("    ProjectName,");
            sql.AppendLine("     ProjectId,");
            sql.AppendLine("    MaterialCode,");
            sql.AppendLine("    IFNULL(SUM(RealNumsWeight), 0) 'RealNumsWeight'");

            sql.AppendLine("    FROM");
            sql.AppendLine("  (");
            sql.AppendLine("   SELECT");

            sql.AppendLine("       e.`Name` ProjectName,");
            sql.AppendLine("    e.Id ProjectId,");
            sql.AppendLine("    M.`Name` MaterialName,");
            sql.AppendLine("    M.`Code` MaterialCode,");
            sql.AppendLine("     a.ParentId,");
            sql.AppendLine("    a.MaterialId AS MaterialId,");
            sql.AppendLine("    a.Nums,");
            sql.AppendLine("    a.Slot,");
            sql.AppendLine("   a.RealNums,");
            sql.AppendLine("            a.RealNums * M.Weight RealNumsWeight,");
            sql.AppendLine("    a.State");


            sql.AppendLine("  FROM");
            sql.AppendLine("     PB_DesignDetail AS a");
            sql.AppendLine("     LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
            sql.AppendLine("    LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");

            sql.AppendLine("      LEFT JOIN PB_Project AS e ON List.ProjectId = e.Id");
            sql.AppendLine("   WHERE");
            sql.AppendLine("     a.State = 0");
            sql.AppendLine("   UNION ALL");
            sql.AppendLine("    SELECT");

            sql.AppendLine("     e.`Name` ProjectName,");
            sql.AppendLine("     e.Id ProjectId,");
            sql.AppendLine("    M.`Name` MaterialName,");
            sql.AppendLine("   M.`Code` MaterialCode,");
            sql.AppendLine("    a.ParentId,");
            sql.AppendLine("    a.MaterialId AS MaterialId,");
            sql.AppendLine("    a.Nums,");
            sql.AppendLine("    a.Slot,");
            sql.AppendLine("    a.RealNums,");
           sql.AppendLine(" a.RealNums * M.Weight RealNumsWeight,");
             sql.AppendLine("        a.State");


           sql.AppendLine("     FROM");
           sql.AppendLine("     PB_DesignDetail AS a");
            sql.AppendLine("    LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
            sql.AppendLine("   LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
            sql.AppendLine("    LEFT JOIN PB_Project AS e ON List.ProjectId = e.Id");
            sql.AppendLine("   WHERE");
            sql.AppendLine("   a.State = 1");
            sql.AppendLine("   UNION ALL");
            sql.AppendLine("    SELECT");

            sql.AppendLine("       e.`Name` ProjectName,");
            sql.AppendLine("    e.Id ProjectId,");
            sql.AppendLine("   M.`Name` MaterialName,");
            sql.AppendLine("   M.`Code` MaterialCode,");
            sql.AppendLine("     a.ParentId,");
            sql.AppendLine("      a.MaterialId AS MaterialId,");
            sql.AppendLine("     a.Nums,");
            sql.AppendLine("   a.Slot,");
            sql.AppendLine("   (-a.RealNums) RealNums,");
            sql.AppendLine("             a.RealNums * M.Weight RealNumsWeight,");
            sql.AppendLine("   a.State");
            sql.AppendLine("   FROM");
            sql.AppendLine("   PB_DesignDetail AS a");
            sql.AppendLine("    LEFT JOIN PB_DesignList AS List ON a.ParentId = List.Id");
            sql.AppendLine("    LEFT JOIN PB_Material AS M ON a.MaterialId = M.Id");
            sql.AppendLine("   LEFT JOIN PB_Project AS e ON List.ProjectId = e.Id");
            sql.AppendLine("    WHERE");
            sql.AppendLine("   a.State = 2");

            sql.AppendLine("   ) AS k");
            sql.AppendLine("    GROUP BY");

            sql.AppendLine("        ProjectName,");
            sql.AppendLine("             ProjectId,");
            sql.AppendLine("    MaterialId,");
            sql.AppendLine("    MaterialName,");
            sql.AppendLine("    MaterialCode");


            sql.AppendLine("   ORDER BY");
            sql.AppendLine("    MaterialCode");
            sql.AppendLine("  ) AS OK");

            sql.AppendLine("    LEFT JOIN(");
            sql.AppendLine("   SELECT");
            sql.AppendLine("    a.MaterialId AS MaterialId,");
            sql.AppendLine("   IFNULL(sum(C.OutNum), 0) IssuedQuantity,");

            sql.AppendLine("     IFNULL(sum(C.OutNum) * m.Weight, 0) IssuedQuantityWeight,");
            sql.AppendLine("   Design.ProjectId");
            sql.AppendLine("    FROM");
            sql.AppendLine("  PB_PickUpList AS a");
            sql.AppendLine("  LEFT JOIN PB_DesignDetail AS List ON a.SubId = List.Id");

            sql.AppendLine("      LEFT JOIN PB_DesignList AS Design ON List.ParentId = Design.Id");
            sql.AppendLine("    LEFT JOIN TD_OutStorage AS TD_OutStorage ON a.ParentId = TD_OutStorage.RefCode");
            sql.AppendLine("     LEFT JOIN TD_OutStorRecord AS C ON TD_OutStorage.Id = C.OutStorId");
            sql.AppendLine("   AND C.MaterialId = List.MaterialId");

            sql.AppendLine("       LEFT JOIN PB_Material AS m ON a.MaterialId = m.Id");
            sql.AppendLine("    GROUP BY");
            sql.AppendLine("   Design.ProjectId,");
            sql.AppendLine("  	 a.MaterialId");

            sql.AppendLine("   ) AS PB_PickUpList ON PB_PickUpList.ProjectId = OK.ProjectId");
            sql.AppendLine("   AND PB_PickUpList.MaterialId = OK.MaterialId");


            sql.AppendLine("    LEFT JOIN(");
            sql.AppendLine("   select o.`Name`, P.Id FROM PB_Project AS P");
            sql.AppendLine("   LEFT JOIN PB_Customer as o on P.Purchaseandsalesunit = o.Id");

            sql.AppendLine("  )			  AS list on list.Id = OK.ProjectId");
            sql.AppendLine("  LEFT JOIN(");
            sql.AppendLine("   SELECT");
            sql.AppendLine("    MaterialId,");
            sql.AppendLine("    ProjectId,");
            sql.AppendLine("   IFNULL(SUM(a.ThisReturnNums ) ,0) 'ThisReturnNums',");
            sql.AppendLine("  	IFNULL(SUM(a.ThisReturnNums * b.Weight), 0)' ThisReturnWeight'");


            sql.AppendLine("   FROM");

            sql.AppendLine("     PB_ReturnDetailLog as a");

            sql.AppendLine("    LEFT JOIN PB_Material as b on a.MaterialId = b.Id");

            sql.AppendLine("   GROUP BY");

            sql.AppendLine("     ProjectId,MaterialId");
            sql.AppendLine("   )		 AS Returnlog on Returnlog.ProjectId = OK.ProjectId AND Returnlog.MaterialId = OK.MaterialId");


            sql.AppendLine("    WHERE PB_PickUpList.IssuedQuantity > 0");


            if (!input.Search.MaterialName.IsNullOrEmpty())
            {
                sql.AppendLine("WHERE  ProjectInfo.`Name` LIKE '%" + input.Search.MaterialName + "%'");
            }
            var res = await Db.GetListBySqlAsync<IT_ProjectLosses>(sql.ToString());
            int pagecount;
            List<IT_ProjectLosses> listpro_down = res.ToList();
            List<IT_ProjectLosses> productes = new List<IT_ProjectLosses>();
            int pagees = listpro_down.Count;
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            #region

          
            #endregion
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();
            return new PageResultData<IT_ProjectLosses> { Data = productes, Total = pagees, };
        }

        #endregion

        #region 重置占用量

        /// <summary>
        /// 重置占用量
        /// </summary>
        /// <returns></returns>
        public async Task ResettingUsageAsync()
        {
            try
            {
                string sql = @"update 
 IT_LocalMaterial as lm LEFT JOIN (
select b.MaterialId,b.LocationId,SUM(b.StockVolume) as StockVolume,SUM(b.ReadyNums) as ReadyNums,(SUM(b.StockVolume)-SUM(b.ReadyNums)) as Nums  from PB_PickUpMain as a
left join PB_PickUpList as b on a.Id=b.ParentId where a.`Status`<3 group by MaterialId,LocationId
)as t on lm.MaterialId=t.MaterialId and lm.LocalId=t.LocationId
 set Nums_R=IFNULL(t.Nums,0)";
                await Db.ExecuteSqlAsync(sql);
            }
            catch (Exception ex)
            {
                if (ex.IsNullOrEmpty())
                {
                    throw new BusException("接口异常");
                }
                throw new BusException(ex.Message);
            }
        }

        #endregion

        #region 私有成员

        #endregion
    }
}